﻿using Oracle.DataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;


    public class OrclQueryExecInterFace
    {
        //数据库连接地址 A数据中心
       //public string connString = EasWsInterface.Properties.Settings.Default.orclsid;
       public string  connString = "User Id=sxdleasdb;password=sxdleasdb;Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.20)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = esdldb)))";

    public OrclQueryExecInterFace(string connstr)
        {
            
        }

        public DataSet OrclQueryRestDataSet(string sql)
        {
            //oracle 查询结果　返回　DataSet
            DataSet retds = new DataSet();
            OracleConnection conn = new OracleConnection(connString);
            try
            {
                conn.Open();
                OracleDataAdapter orclDA = new OracleDataAdapter(sql, conn);
                orclDA.Fill(retds);
                return retds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                conn.Close();
            }
        }

        public string OrclQueryRestHead0(string sql)
        {
        //oracle 查询结果返回 dataSet
        //执行一个查询语句，返回查询结果的第一行第一列
            string res="";
            DataSet retds = new DataSet();
            OracleConnection conn = new OracleConnection(connString);
            try
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand(sql, conn);
                cmd.CommandType = CommandType.Text;
                //cmd.Connection.Open();
                object dtr = cmd.ExecuteScalar();
                if ( (dtr == System.DBNull.Value) || (dtr==null) )
                {
                    res = "";
                }
                else
                {
                    res = dtr.ToString();
                }
                //cmd.Connection.Close();
                cmd.Dispose();
                conn.Close();
                
            }
            //catch (Exception ex)
            //{
            //    throw new Exception(ex.Message, ex);
            //}
            finally
            {
                conn.Close();
            }

        return res;
    }


        public int OrclDDLExec(string sql)
        {
            //oracle ddl 语句执行　返回影响行数,返回0表示未更新 

            OracleConnection conn = new OracleConnection(connString);
            int row = 0;

            try
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand(sql,conn);
                //cmd.CommandType = CommandType.Text;       
                row = cmd.ExecuteNonQuery();
                conn.Close();
                return row;
            }
            //catch (Exception ex)
            //{
            //    throw new Exception(ex.Message, ex);
            //}
            finally
            {
                conn.Close();
            }

        }
    }